package com.genius.dingding.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Set;

import javax.servlet.http.HttpSession;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.adminmanager.other.entity.FillAddJson;
import com.genius.adminmanager.other.entity.ResponseJson;
import com.genius.adminmanager.other.entity.SessionJson;
import com.genius.adminmanager.user.entity.UserJson;
import com.genius.dingding.service.DingdingServiceAdapter;
import com.genius.dingding.util.GeniusConfig;
import com.genius.workflow.util.ConnectionException;
import com.genius.workflow.util.DBClosedException;
import com.genius.workflow.util.DBTools;
import com.genius.workflow.util.DBTools2;
import com.genius.workflow.util.DBUtils;
import com.genius.workflow.util.PasswordMD5;
@Service("dingdingService")
public class DingdingServiceImpl extends DingdingServiceAdapter{
	@Override
	public JSONObject addUser(JSONObject json, JSONObject response) {
		// 先查询主键是否存在
		 Connection conn = null;
    	try {
    		boolean result = false;
    		//打开连接
        	conn = DBUtils.getConnect();
        	String table = "dingtalk_user";
        	//查询是否存在
        	String sql = "select userid from "+table+" a where userid='"+ json.getString("userid") + "'";
        	ResultSet rs = DBUtils.query(conn, sql);
        	if (rs.next()) {
				result = true;
			}
        	rs.close();
        //存在更改编号和修改时间
        	String field = null;
			if(result){
				field = "jobnumber,xgsj=now()";
				String whereField = "userid";
				DBTools2.update(field, whereField, table, json);
			}else{
				field = "userid,name,username,password,department,mobile,email,sign,jobnumber,lrsj=now(),xgsj=now()";
				json.put("username", json.getString("mobile"));
				json.put("sign", "1");
				json.put("password", "123456");
				DBTools2.insert(field, table, json);
			}
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		} finally {
			DBUtils.closeDBResources(conn);
		}
		return response;
	}
	@Override
	public JSONObject addUserinTUser(JSONArray json, JSONObject response) {
		// 先查询主键是否存在
		 Connection conn = null;
    	try {
    		boolean result = false;
    		//打开连接
        	conn = DBUtils.getConnect();
        	DBUtils.beginTransaction(conn);
        	String table = "t_user";
        	StringBuilder insertSql = new StringBuilder("replace into "+table+"(userId,name,username,password,command,sex,mail,phone,status,createTime,groupId,jobnumber,remark,company) values ");
        	int num = 0;
        	for (int i = 0; i < json.size(); i++) {
        		JSONObject userJson = json.getJSONObject(i);
        		//查询是否存在
	        	String sql = "select userId from "+table+" a where userId='"+ userJson.getString("userid") + "'";
	        	ResultSet rs = DBUtils.query(conn, sql);
	        	if (rs.next()) {
					result = true;
				}
	        	rs.close();
	        //存在更改编号和修改时间
				if(!result){
					num++;
					String password = PasswordMD5.MD5Pwd(userJson.getString("mobile"), "123456");
					insertSql.append("('"+userJson.getString("userid")+"','"+userJson.getString("name")+"','"+userJson.getString("mobile")+"'"
							+ ",'"+password+"','123','','"+userJson.getString("email")+"','"+userJson.getString("mobile")+"','1',now(),''"
									+ ",'"+userJson.getString("jobnumber")+"','','qiyegongzuo'),");
//							System.out.println(userJson.getString("userid")+"--"+userJson.getString("name"));
				}
				result = false;
			}
        	//存在可插入数据时进行插入
        	if(num > 0){
        		DBUtils.commitOne(conn, insertSql.substring(0, insertSql.length()-1));
        	}
        	DBUtils.endTransaction(conn);
        	response.put(ResponseJson.code, "0");
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, "同步钉钉人员成功");
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		} finally {
			DBUtils.closeDBResources(conn);
		}
		return response;
	}

	@Override
	public JSONObject addMendianUserinTUser(JSONArray json, JSONObject response) {
		Connection conn = null;
    	try {
    		boolean result = false;
    		//打开连接
        	conn = DBUtils.getConnect();
        	DBUtils.beginTransaction(conn);
        	String table = "t_user";
        	StringBuilder insertSql = new StringBuilder("replace into "+table+"(userId,name,username,password,command,sex,mail,phone,status,createTime,groupId,jobnumber,remark,company) values ");
        	int num = 0;
        	for (int i = 0; i < json.size(); i++) {
        		JSONObject userJson = json.getJSONObject(i);
        		//查询是否存在
	        	String sql = "select userId from "+table+" a where userId='"+ userJson.getString("userid") + "'";
	        	ResultSet rs = DBUtils.query(conn, sql);
	        	if (rs.next()) {
					result = true;
				}
	        	rs.close();
	        //存在更改编号和修改时间
				if(!result){
					num++;
					String password = PasswordMD5.MD5Pwd(userJson.getString("mobile"), "123456");
					insertSql.append("('"+userJson.getString("userid")+"','"+userJson.getString("name")+"','"+userJson.getString("mobile")+"'"
							+ ",'"+password+"','123','','"+userJson.getString("email")+"','"+userJson.getString("mobile")+"','1',now(),''"
									+ ",'"+userJson.getString("jobnumber")+"','','jindushenghuo'),");
//							System.out.println(userJson.getString("userid")+"--"+userJson.getString("name"));
				}
				result = false;
			}
        	//存在可插入数据时进行插入
        	if(num > 0){
        		DBUtils.commitOne(conn, insertSql.substring(0, insertSql.length()-1));
        	}
        	DBUtils.endTransaction(conn);
        	response.put(ResponseJson.code, "0");
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, "同步钉钉人员成功");
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		} finally {
			DBUtils.closeDBResources(conn);
		}
		return response;
	}
	
	@Override
	public JSONObject opeRecive(JSONObject plainTextJson,HttpSession session) {
		String eventType = plainTextJson.getString("EventType");//获取类型
		switch (eventType) {
		case "bpms_task_change": //审批实例开始，结束
			String result = plainTextJson.getString("result")==null?"":plainTextJson.getString("result");
//			 String processInstanceId = plainTextJson.getString("processInstanceId");
//        	 String type = plainTextJson.getString("type");
//        	 String remark = plainTextJson.getString("remark");
//        	 String bizCategoryId = plainTextJson.getString("bizCategoryId");
//        	 String createTime = plainTextJson.getString("createTime");
//        	 String finishTime = plainTextJson.getString("finishTime");
//        	 String staffId = plainTextJson.getString("staffId");
//        	 String processCode = plainTextJson.getString("processCode");
//        	 String title = plainTextJson.getString("title");
        	 //BUG审批
        	 if(plainTextJson.getString("processCode").equals("PROC-ABC09FE2-5A4B-4C67-A23D-3B7002E30D9D")){
        		 plainTextJson.put("shenpiType", "bugshenpi");
        		 //审批完成并且是成功
        		 if(plainTextJson.getString("type").equals("finish") && "agree".equals(result)){
    				 
        		}
        	//推迟最晚应完成日期审批
        	 }else if(plainTextJson.getString("processCode").equals("PROC-C93C129A-7568-4DB1-88B3-10928AC37D1F")){
        		 plainTextJson.put("shenpiType", "tuichizuiwanyingwanchengriqi");
        		 if(plainTextJson.getString("type").equals("finish") && "agree".equals(result)){
    				
        		 }
        	 }
        	 //插入审批状态表
        	 StringBuilder insertField = new StringBuilder();
        	 for(String key : plainTextJson.keySet()){
        		    insertField.append(key+",");
        		}
        	 insertField.append("userId");
        	 JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
        	 plainTextJson.put("userId", userJson.getString(UserJson.lrr));
        	 try {
				DBTools2.insert(insertField.toString(), "shenpi_flag", plainTextJson);
			} catch (ConnectionException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (DBClosedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			break;
		default:
			break;
		}
		return null;
	}


	@Override
	public JSONObject getYingyunUser(JSONObject json, JSONObject response, HttpSession session) {
		try {
			StringBuilder tiaojianSql = new StringBuilder();
			if(json.getString("job") != null && !json.getString("job").equals("null") && !json.getString("job").equals("")){
				tiaojianSql.append( " and job='"+json.getString("job")+"' ");
			}
			String mid = "";
			if(json.getString("yearmonth") != null && !json.getString("yearmonth").equals("null") && !json.getString("yearmonth").equals("")){
				mid = "_his";
				tiaojianSql.append( "and yearmonth = '"+json.getString("yearmonth")+"'");
			}
			String jobnumber = json.getString("jobnumber");
			if(jobnumber == null || jobnumber.equals("") || jobnumber.equals("null")){
				JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
				jobnumber = userJson.getString(UserJson.jobnumber);
				userJson = null;
			}
			String sql = "select name,job,jobname,pjob,pjobname,pjobnumber,cjob,cjobname from "
					+ GeniusConfig.ZHIBIAODATABASE+".yingyun_user_job"+mid+" where "
					+ "jobnumber = '"+jobnumber+"' "+tiaojianSql+" order by orderType asc limit 1";
			
    		
    		JSONObject resultJson = DBTools.select(sql);
    		if(resultJson == null){
    			resultJson = new JSONObject();
    			resultJson.put("job", "Store");
    			resultJson.put("pjob", "Employee");
    		}
    		jobnumber = null;
    		sql = null;
    		tiaojianSql = null;
    		mid = null;
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Success);
			response.put(ResponseJson.data,resultJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Edit_Fill_Data_Error);
		}
		return response;
	}

	@Override
	public JSONObject getspwlUser(JSONObject json, JSONObject response, HttpSession session) {
		try {
			String jobnumber = json.getString("jobnumber");
			if(jobnumber == null || jobnumber.equals("") || jobnumber.equals("null")){
				JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
				jobnumber = userJson.getString(UserJson.jobnumber);
				userJson = null;
			}
			String sql = "select name,job,pjob,pjobnumber,cjob from "+GeniusConfig.ZHIBIAODATABASE+".spwl_user "
					+ " where jobnumber = '"+jobnumber+"' and userType='caigou'";
			
    		
    		JSONObject resultJson = DBTools.select(sql);
    		jobnumber = null;
    		sql = null;
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Success);
			response.put(ResponseJson.data,resultJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Edit_Fill_Data_Error);
		}
		return response;
	}
	


	@Override
	public JSONObject getYingyunUserJd(JSONObject json, JSONObject response, HttpSession session) {
		try {
			StringBuilder tiaojianSql = new StringBuilder();
			if(json.getString("job") != null && !json.getString("job").equals("null") && !json.getString("job").equals("")){
				tiaojianSql.append( " and job='"+json.getString("job")+"' ");
			}
			String mid = "";
			if(json.getString("yearmonth") != null && !json.getString("yearmonth").equals("null") && !json.getString("yearmonth").equals("")){
				mid = "_his";
				tiaojianSql.append( "and yearmonth = '"+json.getString("yearmonth")+"'");
			}
			String jobnumber = json.getString("jobnumber");
			if(jobnumber == null || jobnumber.equals("") || jobnumber.equals("null")){
				JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
				jobnumber = userJson.getString(UserJson.jobnumber);
				userJson = null;
			}
			String sql = "select name,job,jobname,pjob,pjobname,pjobnumber,cjob,cjobname from "
					+ GeniusConfig.JINDUZHIBIAODATABASE+".yingyun_user_job"+mid+" where "
					+ "jobnumber = '"+jobnumber+"' "+tiaojianSql+" order by orderType asc limit 1";
			
    		
    		JSONObject resultJson = DBTools.select(sql);
    		if(resultJson == null){
    			resultJson = new JSONObject();
    			resultJson.put("job", "Store");
    			resultJson.put("pjob", "Employee");
    		}
    		jobnumber = null;
    		sql = null;
    		tiaojianSql = null;
    		mid = null;
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Success);
			response.put(ResponseJson.data,resultJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Edit_Fill_Data_Error);
		}
		return response;
	}
}
